USE [hr]
GO
sp_dbcmptlevel [hr], 90
GO
DECLARE @db NVARCHAR(500)
SET @db = '[hr]'


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Id_GenerateChar]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[Id_GenerateChar]
	
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Id_GenerateBinary]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION [dbo].[Id_GenerateBinary]
	
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'App.Id.Clr.dll' and is_user_defined = 1)
	DROP ASSEMBLY [App.Id.Clr.dll]

EXEC sp_configure 'show advanced options','1'; 
RECONFIGURE; 
EXEC sp_configure 'clr enabled','1' 
RECONFIGURE; 

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'ALTER DATABASE '+@db+' SET TRUSTWORTHY ON'
exec sp_executesql @sql


declare @user varchar(50)
SELECT  @user = quotename(SL.Name)
  FROM  master..sysdatabases SD inner join master..syslogins SL
    on  SD.SID = SL.SID
 Where  SD.Name = DB_NAME()
exec('exec sp_changedbowner ' + @user)


CREATE ASSEMBLY [App.Id.Clr.dll]
AUTHORIZATION [dbo]
FROM 'C:\bin\App.Id.Clr.dll'
WITH PERMISSION_SET = UNSAFE

GO

CREATE FUNCTION [dbo].[Id_GenerateChar]
(
	@rand FLOAT
)
RETURNS NCHAR(24)
AS
	EXTERNAL NAME [App.Id.Clr.dll].[App.Id].[GenerateChar]
GO   

CREATE FUNCTION [dbo].[Id_GenerateBinary]
(
	@rand FLOAT
)
RETURNS BINARY(12)
AS
	EXTERNAL NAME [App.Id.Clr.dll].[App.Id].[GenerateBinary]